﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace Blog_DAL
{
    /// <summary>
    /// 博客文章数据库操作类
    /// </summary>
    public class BlogArticleDAL : IDbDal
    {
        /// <summary>
        /// 返回单个博客文章
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.BlogArticle</typeparam>
        /// <param name="t">泛型:Blog_MODEL.BlogArticle</param>
        /// <returns></returns>
        public T Query<T>(T t)
        {
            Blog_MODEL.BlogArticle BArticle = t as Blog_MODEL.BlogArticle;
            String strSql = "select * from BlogArticle where Id=@Id and 1=1 ";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<T>(strSql, BArticle).SingleOrDefault();
            }
        }
        /// <summary>
        /// 查询多个个博客文章
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.BlogArticle</typeparam>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public List<T> QueryList<T>(T t)
        {
            Blog_MODEL.BlogArticle BArticle = t as Blog_MODEL.BlogArticle;
            var Parameters = new DynamicParameters();
            StringBuilder strSql = new StringBuilder(20);
            strSql.AppendLine("select * from BlogArticle where 1=1");
            //拼接条件
            if (!string.IsNullOrEmpty(BArticle.BlogArticleTitle))
            {
                strSql.AppendLine(" and UPPER(BlogArticleTitle) = @BlogArticleTitle");
                Parameters.Add("BlogArticleTitle", BArticle.BlogArticleTitle, DbType.String);
            }
            if (!string.IsNullOrEmpty(BArticle.BlogArticleBody))
            {
                strSql.AppendLine(" and BlogArticleBody like @BlogArticleBody");
                Parameters.Add("BlogArticleBody", "%" + BArticle.BlogArticleBody + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(BArticle.BlogArticleBody_Markdown))
            {
                strSql.AppendLine(" and BlogArticleBody_Markdown like @BlogArticleBody_Markdown");
                Parameters.Add("BlogArticleBody_Markdown", "%" + BArticle.BlogArticleBody_Markdown + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(BArticle.BlogArticleNote))
            {
                strSql.AppendLine(" and BlogArticleNote like @BlogArticleNote");
                Parameters.Add("BlogArticleNote", "%" + BArticle.BlogArticleNote + "%", DbType.String);
            }
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<T>(strSql.ToString(), Parameters).ToList();
            }
        }
        /// <summary>
        /// 删除单个博客文章
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.BlogArticle</typeparam>
        /// <param name="t">泛型:Blog_MODEL.BlogArticle</param>
        /// <returns></returns>
        public Int32 Delete<T>(T t)
        {
            Blog_MODEL.BlogArticle BArticle = t as Blog_MODEL.BlogArticle;
            String strSql = "delete from BlogArticle where Id=@Id ";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Execute(strSql, BArticle);
            }

        }
        /// <summary>
        /// 修改单个博客文章
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.BlogArticle</typeparam>
        /// <param name="t">泛型:Blog_MODEL.BlogArticle</param>
        /// <returns></returns>
        public Int32 Update<T>(T t)
        {
            Blog_MODEL.BlogArticle BArticle = t as Blog_MODEL.BlogArticle;
            String strSql = @"update BlogArticle set BlogArticleTitle=@BlogArticleTitle,
                                                         BlogArticleBody=@BlogArticleBody,
                                                         BlogArticleBody_Markdown=@BlogArticleBody_Markdown,
                                                         BlogArticleSortValue=@BlogArticleSortValue,
                                                         BlogArticleNote=@BlogArticleNote where Id=@Id";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Execute(strSql, BArticle);
            }
        }
        public Int32 addBrowsingNum<T>(T t)
        {
            Blog_MODEL.BlogArticle BArticle = t as Blog_MODEL.BlogArticle;
            String strSql = @"update BlogArticle set BlogArticleBrowsingNum=@BlogArticleBrowsingNum,
                                                     BlogArticleLikeNum=@BlogArticleLikeNum,
                                                     BlogArticleCommentNum=@BlogArticleCommentNum  
                                                         where Id=@Id";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Execute(strSql, BArticle);
            }
        }

        /// <summary>
        /// 插入博客文章
        /// </summary>
        /// <typeparam name="T">泛型:Blog_MODEL.BlogArticle</typeparam>
        /// <param name="t">泛型:Blog_MODEL.BlogArticle</param>
        /// <returns></returns>
        public Int32 Insert<T>(T t)
        {
            Blog_MODEL.BlogArticle BArticle = t as Blog_MODEL.BlogArticle;
            String strSql = "INSERT INTO BlogArticle(BlogArticleTitle,BlogArticleBody,BlogArticleBody_Markdown,BlogArticleSortValue,BlogArticleNote) VALUES(@BlogArticleTitle,@BlogArticleBody,@BlogArticleBody_Markdown,@BlogArticleSortValue,@BlogArticleNote) select @@identity ";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<int>(strSql, BArticle).First();
            }
        }
        /// <summary>
        /// 插入标签关联表
        /// </summary>
        /// <returns></returns>
        public Int32 InsertBlogRelated(Blog_MODEL.BlogRelated bRelated)
        {
            String strSql = "INSERT INTO BlogRelated(BlogRelatedArticleId,BlogRelateLableId,BlogArticleNote) VALUES(@BlogRelatedArticleId,@BlogRelateLableId,@BlogArticleNote) select @@identity ";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                return connection.Query<int>(strSql, bRelated).First();
            }
        }
        /// <summary>
        /// 获取最大文章排序值
        /// </summary>
        /// <returns></returns>
        public Int32 getBlogSortMax()
        {
            using (var connection = Blog_DAL.ConnectionFctory.OpenConnection())
            {
                return connection.ExecuteScalar<int>("select max(BlogArticleSortValue) from BlogArticle");
            }
        }
        /// <summary>
        /// 添加博客文章
        /// </summary>
        /// <param name="_BlogRelated"></param>
        /// <param name="_BlogArticle"></param>
        /// <returns></returns>
        public Int32 InsertBlogArticleTransaction(List<Blog_MODEL.BlogLable> listBlogLable, Blog_MODEL.BlogArticle _BlogArticle)
        {
            int i = 1;
            StringBuilder strSqlInsertBlogRelated = new StringBuilder(20);
            String strSqlInsertBlogArticle = "INSERT INTO BlogArticle(BlogArticleTitle,BlogArticleBody,BlogArticleBody_Markdown,BlogArticleSortValue,BlogArticleNote) VALUES(@BlogArticleTitle,@BlogArticleBody,@BlogArticleBody_Markdown,@BlogArticleSortValue,@BlogArticleNote) select @@identity ";
            using (var connection = ConnectionFctory.OpenConnection())
            {
                IDbTransaction tran = connection.BeginTransaction();
                try
                {
                    //博客文章id
                    int BlogRelatedArticleId = connection.Query<int>(strSqlInsertBlogArticle, _BlogArticle, tran).First();
                    strSqlInsertBlogRelated.AppendLine("INSERT INTO BlogRelated(BlogRelatedArticleId,BlogRelateLableId) ");
                    foreach (var BlogLable in listBlogLable)
                    {
                        if (BlogLable != listBlogLable[listBlogLable.Count - 1])
                        {

                            strSqlInsertBlogRelated.AppendFormat(" SELECT {0},{1} UNION ALL ", BlogRelatedArticleId, BlogLable.BlogLableBh);
                        }
                        else
                        {
                            strSqlInsertBlogRelated.AppendFormat(" SELECT {0},{1} select @@identity", BlogRelatedArticleId, BlogLable.BlogLableBh);
                        }
                    }
                    i = connection.Query<int>(strSqlInsertBlogRelated.ToString(), null, tran).First();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    i = 0;
                }
                return i;
            }
        }
        /// <summary>
        /// 修改博客文章
        /// </summary>
        /// <param name="_BlogRelated"></param>
        /// <param name="_BlogArticle"></param>
        /// <returns></returns>
        public Int32 UpdateBlogArticleTransaction(List<Blog_MODEL.BlogLable> listBlogLable, Blog_MODEL.BlogArticle _BlogArticle)
        {
            int i = 1;
            StringBuilder strSqlInsertBlogRelated = new StringBuilder(20);
            String strSqlBlogArticle = @"update BlogArticle set BlogArticleTitle=@BlogArticleTitle,
                                                         BlogArticleBody=@BlogArticleBody,
                                                         BlogArticleBody_Markdown=@BlogArticleBody_Markdown,
                                                         BlogArticleSortValue=@BlogArticleSortValue,
                                                         BlogArticleNote=@BlogArticleNote where Id=@Id";

            using (var connection = ConnectionFctory.OpenConnection())
            {
                IDbTransaction tran = connection.BeginTransaction();
                try
                {
                    connection.Execute(strSqlBlogArticle, _BlogArticle, tran);
                    strSqlInsertBlogRelated.AppendFormat(" DELETE FROM  BlogRelated WHERE BlogRelatedArticleId={0} ", _BlogArticle.Id);
                    strSqlInsertBlogRelated.AppendLine("INSERT INTO BlogRelated(BlogRelatedArticleId,BlogRelateLableId) ");
                    foreach (var BlogLable in listBlogLable)
                    {
                        if (BlogLable != listBlogLable[listBlogLable.Count - 1])
                        {

                            strSqlInsertBlogRelated.AppendFormat(" SELECT {0},{1} UNION ALL ", _BlogArticle.Id, BlogLable.BlogLableBh);
                        }
                        else
                        {
                            strSqlInsertBlogRelated.AppendFormat(" SELECT {0},{1} select @@identity", _BlogArticle.Id, BlogLable.BlogLableBh);
                        }
                    }
                    i = connection.Query<int>(strSqlInsertBlogRelated.ToString(), null, tran).First();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    i = 0;
                }
                return i;
            }
        }
        /// <summary>
        /// 获取文章条数
        /// </summary>
        /// <param name="BlogArticle"></param>
        /// <returns></returns>
        public Int32 getBlogArticleCount(Blog_MODEL.BlogArticle BlogArticle)
        {
            var Parameters = new DynamicParameters();
            StringBuilder strSql = new StringBuilder(20);
            strSql.AppendLine("select COUNT(1) AS NUM from BlogArticle where 1=1");
            //拼接条件
            if (!string.IsNullOrEmpty(BlogArticle.BlogArticleTitle))
            {
                strSql.AppendLine(" and BlogArticleTitle like @BlogArticleTitle");
                Parameters.Add("BlogArticleTitle", "%" + BlogArticle.BlogArticleTitle + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(BlogArticle.BlogArticleBody))
            {
                strSql.AppendLine(" and BlogArticleBody like @BlogArticleBody");
                Parameters.Add("BlogArticleBody", "%" + BlogArticle.BlogArticleBody + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(BlogArticle.BlogArticleBody_Markdown))
            {
                strSql.AppendLine(" and BlogArticleBody_Markdown like @BlogArticleBody_Markdown");
                Parameters.Add("BlogArticleBody_Markdown", "%" + BlogArticle.BlogArticleBody_Markdown + "%", DbType.String);
            }
            if (!string.IsNullOrEmpty(BlogArticle.BlogArticleNote))
            {
                strSql.AppendLine(" and BlogArticleNote like @BlogArticleNote");
                Parameters.Add("BlogArticleNote", "%" + BlogArticle.BlogArticleNote + "%", DbType.String);
            }
            using (var connection = Blog_DAL.ConnectionFctory.OpenConnection())
            {
                return connection.ExecuteScalar<int>(strSql.ToString(), Parameters);
            }
        }
        /// <summary>
        /// 查询博客标签
        /// </summary>
        /// <param name="BlogRelatedArticleId">博客文章Id</param>
        /// <returns></returns>
        public List<Blog_MODEL.BlogLable> QueryBlogLable(int BlogRelatedArticleId)
        {
            var Parameters = new DynamicParameters();
            String strSql = @"SELECT * FROM BlogLable B WHERE 
                                exists(
                                SELECT B1.BlogRelateLableId FROM BlogRelated B1
                                where B.BlogLableBh=B1.BlogRelateLableId
                                AND B1.BlogRelatedArticleId=@BlogRelatedArticleId
                                )";
            Parameters.Add("BlogRelatedArticleId", BlogRelatedArticleId, DbType.Int32);
            using (var connection = Blog_DAL.ConnectionFctory.OpenConnection())
            {
                return connection.Query<Blog_MODEL.BlogLable>(strSql, Parameters).ToList();
            }
        }
        /// <summary>
        /// 获取置顶博客
        /// </summary>
        /// <returns></returns>
        public Blog_MODEL.BlogArticle getObenBlogArticle()
        {
            using (var connection = Blog_DAL.ConnectionFctory.OpenConnection())
            {
                return connection.Query<Blog_MODEL.BlogArticle>(" SELECT * FROM BlogArticle WHERE BlogArticle.BlogArticleSortValue=(SELECT MAX(BlogArticleSortValue) FROM BlogArticle)").ElementAt(0);
            }
        }
        /// <summary>
        /// 博客详情
        /// </summary>
        /// <returns></returns>
        public List<Blog_MODEL.BlogArticle> getBlogArticleShow(int Id)
        {
            string sql = string.Format(@"SELECT (select STUFF(( SELECT ','+B.BlogLableName FROM (SELECT B.BlogLableName FROM BlogLable B WHERE 
                                                    exists(
                                                    SELECT B1.BlogRelateLableId FROM BlogRelated B1
                                                    where B.BlogLableBh=B1.BlogRelateLableId
                                                AND B1.BlogRelatedArticleId=BA.Id)) B  FOR XML PATH('')),1,1,'')) as BlogLableNames,*
                                                FROM BlogArticle BA WHERE BA.Id={0}    union all
select * from (select top 1 '' BlogLableNames,* from BlogArticle where Id<{0} order by BlogArticleCreateTime desc) t
union all
select * from (select top 1 '' BlogLableNames,* from BlogArticle where Id>{0} order by BlogArticleCreateTime) t", Id);
            using (var connection = Blog_DAL.ConnectionFctory.OpenConnection())
            {
                return connection.Query<Blog_MODEL.BlogArticle>(sql).ToList();
            }
        }
    }
}
